
package com.timber.util;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Collections;
import java.util.Comparator;
import java.util.HashMap;
import java.util.Iterator;
import java.util.StringTokenizer;
import java.util.Vector;

import javax.naming.InitialContext;
import javax.sql.DataSource;

/**
 * Base class to extract and hold rows of data from database. Creates objects
 * based on the value of <b>className</b> constructor parameter. All objects
 * must extend SLDataObject and implement the following: <pre hashKey() method.
 * Used to build byHashKey map load( ResultSet ) method to read row data getId()
 * optional method returns long id. used to load byIdHash when buildIdHash is
 * true
 * 
 * </pre>
 * 
 * The class can be used to connect directly to the database server or use
 * DataSource. DataSource is set when used by WebApp. Othwewise... Database
 * requests are directed to PRIMARY as default. When BizDay(
 * <b>setBizDate(java.util.Date d)<\b> ) is set to before ApplicationDate,
 * requests are directed to HISTORICAL server. Otherwise logical dataserverName
 * is used when set( <b>setDataserverName( String s )<\b> )
 * 
 * Stored Procedure name is the second argument.
 * 
 * Stored Proc parameters are stored in a Vector of Objects and SQL statement
 * generated before execution of runProc().
 * 
 * runProc( Vector params ) method is available.
 * 
 * Data can be sorted by setting Comparator( standard Java object ).
 * 
 * keyVector and displayVector are not filled until first request unless
 * lazyInit is turned off.
 * 
 * extending class can implement postLoad() method called after data is loaded
 * 
 * extends SLDataChangeListenerMgr to manage listner objects
 * 
 */
public class SLDataRecordsMgr extends SLDataChangeListenerMgr implements
		SLDataSet {

	protected int initCapacity = 100;

	protected int capacityIncr = 100;

	protected java.util.Date bizDay; // = Global.getApplicationDate();

	protected java.util.Date lastBizDay = null;

	/**
	 * logical dataserver name. NULL values ==> PRIMARY
	 */
	protected String dataserverName;

	/** hash map based on Object.hashKey() value */
	protected HashMap byHashKey;

	/** hash map based on Object.getId() value, only created if idHash is true */
	protected HashMap byIdHash;

	/** vector of hash keys from byHashKey map */
	protected Vector keyVector;

	/** vector of Strings from Object.listString(). Used in selection lists, etc. */
	protected Vector displayVector;

	/** vector holding data objects */
	protected Vector dataVector;

	protected String sql;

	/** stored proc name to get data */
	//protected String sproc;

	/** Class name for data objects to be created */
	protected String clName;

	private SLProcDescriptor procDescriptor;

	protected Statement stmt;

	protected String _name;

	/** Class object for clName. used to create new instances */
	protected Class _class;

	protected boolean clearHash = true;

	/** set in the constructor when used from WebbApp */
	protected DataSource dataSource = null;

	private String useServer = null;

	/** create vector of hash keys ? */
	boolean loadKeys = true;

	/** create vector of string for dropdown lists ? */
	boolean loadDisplay = true;

	/** build hashMap by object id ? */
	boolean idHash = false;

	/** when true, access methods try to load data if dataVector is empty */
	boolean lazyInit = true;

	/** hold comparator reference */
	public Comparator compObj;

	/**
	 * @param className
	 *            class name of object to hold
	 * @param sql
	 *            initial sql statement
	 */
	public SLDataRecordsMgr(String className, String sql) {
		this(className, sql, 100, 100);
	}

	public SLDataRecordsMgr(String ds, String className, String sql) {
		this(className, sql, 100, 100);
		setupDataSource(ds);
	}

	/**
	 * @param className
	 *            class name of object to hold
	 * @param sql
	 *            initial sql statement
	 * @param initCap
	 *            initial capacity
	 * @param capInc
	 *            capacity increment
	 */
	public SLDataRecordsMgr(String className, String sql, int initCap,
			int capInc) {
		
		procDescriptor = new SLProcDescriptor(sql);
		
		initCapacity = initCap;
		capacityIncr = capInc;
		clear();
		if (className != null)
			clName = className;
		else
			clName = "com.db.gef.util.SLDataStringPair";
		
		

		this.sql = sql;
	}

	/**
	 * @param className
	 *            class name of object to hold
	 * @param sql
	 *            initial sql statement
	 */
	public SLDataRecordsMgr(String className, String sql, boolean buildIdHash) {
		this(className, sql, 100, 100);
		idHash = buildIdHash;
	}

	/**
	 * @param sql
	 *            initial sql statement create data manager to load and hold a
	 *            list of String codes, and optional Descriptions ( e.i.
	 *            SLDataStringPair objects ).
	 */
	public SLDataRecordsMgr(String sql) {
		this(null, sql, 100, 100);
	}

	/**
	 * copy constructor
	 */
	public SLDataRecordsMgr(SLDataRecordsMgr aDM) {
		clear();
		clName = aDM.clName;
		procDescriptor = new SLProcDescriptor(aDM.getProcName());
		sql = new String(aDM.sql);
		byHashKey = new HashMap(aDM.byHashKey);
		keyVector = new Vector(aDM.keyVector);
		displayVector = new Vector(aDM.displayVector);
		dataVector = new Vector(aDM.dataVector);
	}

	public void setupDataSource(String ds) {
		try {
			InitialContext ctx = new InitialContext();
			dataSource = (DataSource) ctx.lookup(ds);
		} catch (Exception ex) {
			ex.printStackTrace();
		}
	}

	public String getSQL() {
		return sql;
	}

	/**
	 * set SQL command
	 * 
	 * @param sqlStr
	 *            sql statement
	 */
	public void setSQL(String sqlStr) {
		sql = sqlStr;
	}

	public String getName() {
		return _name;
	}

	public void setName(String s) {
		_name = s;
	}

	public String getClassName() {
		return clName;
	}

	public void setClassName(String s) {
		clName = s;
		_class = null;
	}

	/**
	 * concat parameters to exesting sql statement, assuming sql is a procedure
	 * call
	 * 
	 * @param paramStr
	 *            parameter(s) string
	 */
	public void addParams(String paramStr) {
		sql = procDescriptor.getProcName().concat(" ").concat(paramStr);
	}

	/**
	 * initialize, get data, only when the data is requested
	 * 
	 * @param on
	 */
	public void setLazyInit(boolean on) {
		lazyInit = on;
	}

	/**
	 * clear stored proc parameters vector
	 */
	public void clearProcP() {
		procDescriptor.clearProcP();
	}

	/**
	 * add object to stored proc parameters vector pls use addProcParameter(
	 * Object o )
	 */
	public void addProcP(Object o) {
		procDescriptor.addProcP(o);
	}

	/**
	 * add paremeter object to procedure parameters vector
	 * 
	 * @param o
	 */
	public void addProcParameter(Object o) {
		procDescriptor.addProcParameter(o);
	}

	/**
	 * set procedure paramters vector to aV
	 */
	public void setProcParams(Vector aV) {
		procDescriptor.setProcParams(aV);
	}

	public String getProcs() {
		return procDescriptor.getProcName();
	}

	/** @deprecated */
	public void setProcs(String aS) {
		procDescriptor.setProcName(aS);
	}

	public void setProcName(String s) {
		procDescriptor.setProcName(s);
	}

	/**
	 * get stored proc name
	 * 
	 * @return
	 */
	public String getProcName() {
		return procDescriptor.getProcName();
	};

	/**
	 * set business day for the data request. setting to before( ApplicationDate )
	 * directs request to HISTORICAL dataserver
	 */
	public java.util.Date getBizDate() {
		return bizDay;
	}

	public void setBizDate(java.util.Date d) {
		bizDay = d;
	}

	/**
	 * set logical database server name
	 */
	public void setDataserverName(String s) {
		dataserverName = s;
	}

	/**
	 * *
	 * 
	 * @param params
	 *            Vector of objects for each proc parameter
	 */
	public void runProc(Vector params) throws Exception {
		procDescriptor.setProcParams(params);

		runProc();
	}

	/**
	 * build sql from procedure name and parameters vector
	 */
	public void runProc() throws Exception {
		sql = getProcName() + getProcParams();

		init();
	}

	/**
	 * get object by key. when mgr is empty and load is true, load
	 */
	public Object getByKey(Object key, boolean load) {
		try {
			if (byHashKey.isEmpty())
				if (load == true) {
					sql = getProcName() + getProcParams();
					init();
				} else
					return null;

			return (byHashKey.get(key));
		} catch (Exception ex) {
			return null;
		}
	}

	/**
	 * get object by hashKey
	 * 
	 * loads data when empty mgr
	 */
	public Object getByKey(Object key) {
		try {
			if (byHashKey.isEmpty() && lazyInit == true)
				init();

			return (byHashKey.get(key));
		} catch (Exception ex) {
			return null;
		}
	}

	/**
	 * get object based on id
	 */
	public Object getById(Object id) {
		if (byIdHash != null)
			return byIdHash.get(id);

		return null;
	}

	/** @deprecated use addItem( dataO ) */
	public void addRow(SLDataObject dataO) {
		addItem(dataO);
	}

	/**
	 * add item to manager, sort and fire SLDataChange.INSERT event
	 * 
	 * @param SLDataObject
	 *            dataO
	 * 
	 */
	public void addItem(SLDataObject dataO) {
		put(dataO);

		sort();

		fireSLDataAdd(dataO);
	}

	/**
	 * add row to manager
	 * 
	 * @param SLDataObject
	 *            dataO
	 * 
	 */
	public void put(SLDataObject dataO) {
		if (dataO == null)
			return;

		Object key = dataO.hashKey();
		if (key != null)
			byHashKey.put(key, dataO);

		if (idHash == true) {
			if (dataO.getIdObj() != null)
				byIdHash.put(dataO.getIdObj(), dataO);
			else
				byIdHash.put(new Long(dataO.getId()), dataO);
		}

		dataVector.add(dataO);

		loadKeys = true;
		loadDisplay = true;
	}

	/**
	 * add vector of objects to this data manager
	 */
	public void addVector(Vector v) {
		if (v != null && !v.isEmpty()) {
			Iterator it = v.iterator();
			while (it.hasNext())
				put((SLDataObject) it.next());
		}
	}

	/**
	 * set Comparator object
	 * 
	 * see java doc for definition
	 */
	public void setComparator(Comparator c) {
		compObj = c;
	}

	/**
	 * sort dataVector when compObj is set
	 * 
	 * clear keyVector and displayVector
	 */
	public void sort() {
		if (compObj == null)
			return;

		keyVector.clear();
		displayVector.clear();

		loadKeys = true;
		loadDisplay = true;

		Collections.sort(dataVector, compObj);

	}

	/**
	 * sort data based on provided Comparator
	 */
	public void sort(Comparator c) {
		compObj = c;
		sort();
	}

	public void putUnique(SLDataObject aDO) {
		Object temp = null;
		Object key = aDO.hashKey();

		if (key != null) {
			temp = byHashKey.put(key, aDO);
		}

		if (temp != null) {
			int i = dataVector.indexOf(temp);
			dataVector.remove(i);
			dataVector.insertElementAt(aDO, i);
		} else {
			dataVector.add(aDO);
		}

		loadKeys = true;
		loadDisplay = true;
	}

	/**
	 * create new instance of class object
	 */
	public SLDataObject getRowInstance() throws Exception {

		if (_class == null) {
			try {
				_class = Class.forName(clName);
			} catch (ClassNotFoundException ce) {
				Util.error("[SLDataRecordsMgr getRowInstance]", ce.toString());
				throw new Exception("Failed to create class  " + clName);
			}
		}

		SLDataObject obj = (SLDataObject) _class.newInstance();

		if (obj == null)
			throw new Exception("Failed to create " + clName + " object ");

		return obj;
	}

	/**
	 * process result rows, load objects
	 */
	protected int processResultSet(ResultSet rs) throws SQLException,
			java.lang.Exception {
		int rowCnt = 0;
		while (rs.next()) {
			rowCnt++;
			SLDataObject item = getRowInstance();

			if (item.load(rs) == true) {
				put(item);
				postLoad(item);
			}

			if ((rowCnt % 100) == 0)
				updateProgress("Rows processed: " + rowCnt);
		}

		updateProgress("Total rows processed: " + rowCnt);
		rs.close();
		return rowCnt;
	}

	/**
	 * execute SQL and load objectss
	 */
	public void init() throws Exception {
		stmt = null;
		int rowCnt = 0;

		Connection conn = null;

		if (clearHash)
			clear();

		try {
			/*
			 * called from application server environment
			 */
			if (dataSource == null) {
				if (useServer != null)
					conn = Global.getDBConnection(useServer);
				else if (bizDay != null
						&& bizDay.before(Global.getApplicationDate()))
					conn = Global.getDBConnection(Global.HISTORICAL);
				else
					conn = Global.getDBConnection(dataserverName);

			} else
				conn = dataSource.getConnection();

			if (conn == null)
				throw new Exception("[Global.getDBConnection("
						+ bizDay.toString()
						+ ")] Connection for this date is not setup.");

			stmt = conn.createStatement(ResultSet.TYPE_FORWARD_ONLY,
					ResultSet.CONCUR_READ_ONLY);

			lastBizDay = bizDay;

			updateProgress("Query sent. Waiting for results...");
			try {
				Util.log("SLDataRecordsMgr.init()", "exec: "
						+ conn.getMetaData().getURL().substring(
								conn.getMetaData().getURL().lastIndexOf('/') + 1)
						+ "." + sql);
			} catch (RuntimeException e) {/* we dont care this is only debug */	
				Util.log("SLDataRecordsMgr.init()", "params: "
						 + sql);
			}
			if (stmt.executeQuery(sql) != null) {
				Util.log("processing results...");
				System.out.println("sql ----:"+sql);
				//updateProgress("Results received. Processing rows...");
				rowCnt = processResultSet(stmt.getResultSet());
				System.out.println("Row Count :"+rowCnt);
			} else {
				Util.log("Query did not return ResultSet");
			}
			stmt.close();
			stmt = null;
		} catch (SQLException se) {
			Util.error("SLDataRecordsMgr.init()", "SQL Error "
					+ se.getErrorCode() + ",  " + se.getMessage());
			if (stmt != null) {
				stmt.close();
				stmt = null;
			}
			throw new Exception(se.getMessage());
		} catch (Exception ex) {
			Util.error("SLDataRecordsMgr.init()", "For class - " + clName, ex);
			if (stmt != null) {
				stmt.close();
				stmt = null;
			}
			throw new Exception(ex.getMessage());
		} finally {
			if (dataSource != null && conn != null)
				conn.close();
		}

		Util.log("SLDataRecordsMgr.init()", "Row Count:   " + rowCnt);

		sort();

	}

	public void reload() throws Exception {
		init();
	}

	/**
	 * clear all data vectors and hashmaps
	 */
	public void clear() {
		byHashKey = new HashMap(initCapacity);
		if (idHash)
			byIdHash = new HashMap(initCapacity);

		keyVector = new Vector(initCapacity, capacityIncr);
		displayVector = new Vector(initCapacity, capacityIncr);
		dataVector = new Vector(initCapacity, capacityIncr);
		procDescriptor.clearProcP();

		loadKeys = true;
		loadDisplay = true;
	}

	/**
	 * return Vector of Hash keys
	 */
	public Vector getKeyList() // throws Exception
	{
		if (byHashKey.isEmpty() && lazyInit == true)
			try {
				init();
			} catch (Exception ex) {
				return null;
			}

		if (loadKeys == true) {
			keyVector = new Vector(byHashKey.keySet());
			loadKeys = false;
		}

		return keyVector;
	}

	/**
	 * generate Vector of display keys from dataVector. calls listString() on
	 * every item. Items are returned in the order in dataVector
	 * 
	 * add SELECT_ALL as first item in the list
	 * 
	 */
	public Vector getDisplayItems() {
		return getDisplayItems(true);
	}

	/**
	 * generate Vector of display keys from dataVector. calls listString() on
	 * every item. Items are returned in the order in dataVector
	 * 
	 * @param all
	 *            add SELECT_ALL as first item in the list
	 * 
	 */
	public Vector getDisplayItems(boolean all) {
		if (byHashKey.isEmpty() && lazyInit == true)
			try {
				init();
			} catch (Exception ex) {
				return null;
			}

		// if( displayVector.isEmpty() )
		if (loadDisplay == true) {
			// SELECT_ALL in first pos
			if (all == true)
				displayVector.add(SELECT_ALL);

			Iterator it = dataVector.iterator();
			while (it.hasNext())
				displayVector.add(((SLDataObject) it.next()).listString());
			loadDisplay = false;
		}

		return displayVector;
	}

	/**
	 * return data Vector in case vector is empty, i.e. lazy initialization,
	 * execute sql, runProc()
	 */
	public Vector getDataVector() {
		try {
			if ((dataVector == null || dataVector.isEmpty() || (bizDay != null && !bizDay
					.equals(lastBizDay)))
					&& lazyInit == true)
				runProc();

			return dataVector;
		} catch (Exception ex) {
			return null;
		}
	}

	/**
	 * set business date and retern data vector( load when necessary )
	 * 
	 * @param bDay
	 * @return
	 */
	public Vector getDataVector(java.util.Date bDay) {
		bizDay = (bDay == null) ? Global.getApplicationDate() : bDay;
		return getDataVector();
	}

	/**
	 * add Object to display vector
	 */
	public void displayAdd(Object aO) {
		if (displayVector != null)
			displayVector.add(aO);
	}

	/**
	 * return dataset size
	 */
	public int size() {
		return (dataVector != null ? dataVector.size() : 0);
	}

	public boolean isEmpty() {
		return (size() == 0);
	}

	/**
	 * process vector of procParams and build comma seperated procedure
	 * parameters string
	 * @depecated Use SLProcParams instead
	 */
	public String getProcParams() {
		return procDescriptor.getProcParams();
	}

	/**
	 * * remove item from manager
	 */
	public void removeItem(SLDataObject dataO) {
		if (dataO == null)
			return;

		loadKeys = true;
		loadDisplay = true;

		byHashKey.remove(dataO.hashKey());
		dataVector.removeElement(dataO);

		if (byIdHash != null)
			byIdHash.remove(new Long(dataO.getId()));

		fireSLDataDelete(dataO);

	}

	public void removeItemAt(int indx) {
		SLDataObject o = (SLDataObject) dataVector.elementAt(indx);

		removeItem(o);
	}

	/**
	 * turn comma separated string of hash keys to Vector of ids as Longs
	 */
	public Vector getIdVector(String str) {
		Vector v = new Vector();

		StringTokenizer tk = new StringTokenizer(str, ",");

		while (tk.hasMoreTokens()) {
			String s = tk.nextToken();

			SLDataObject o = (SLDataObject) getByKey(s);
			if (o != null && o.getId() != 0)
				v.add(new Long(o.getId()));
		}

		return v;
	}

	/**
	 * cancel database SQL
	 */
	public void cancel() {
		if (stmt != null) {
			try {
				stmt.cancel();
			} catch (Exception ex) {
				Util.error("[SLDataRecordsMgr.cancel()]", "Caught "
						+ ex.getMessage());
			}
		}
	}

	protected void updateProgress(final String note) {
		return;
	}

	public void setClearHash(boolean b) {
		clearHash = b;
	}

	public boolean getClearHash() {
		return clearHash;
	}

	protected void postLoad(SLDataObject obj) {
	}

	public void setUseServer(String s) {
		useServer = s;
	}
}
